In my last article, I demonstrated that starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general purpose files, so that entire Oracle databases can be stored inside Oracle Cloud FS. In addition, for a database with its files stored on Oracle Cloud file system, Oracle ACFS Snapshots may serve as point-in-time backups of the database which can be used for online recovery of database files.
In this article, I will demonstrate that ACFS snapshots can be integrated with RMAN and hence employed to perform complete recovery using RMAN RESTORE / RECOVER commands, thereby complementing RMAN functionality.
Currently I am working in an Oracle Database 12.1.0.2 cluster environment and have created a database named cfsdb, with all of its files stored on the cloud file system as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
[oracle@host01 ~]$ srvctl config database -d cfsdb Database unique name: cfsdb Database name: cfsdb Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1 Oracle user: oracle <strong><span style="color: red;">Spfile: /mnt/acfs/oradata/cfsdb/spfilecfsdb.ora</span></strong> <strong><span style="color: red;">Password file: /mnt/acfs/oradata/cfsdb/orapwcfsdb</span></strong> Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: Disk Groups: <strong><span style="color: red;">Mount point paths: /mnt/acfs</span></strong> Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: oper Database instances: cfsdb1 Configured nodes: host01 Database is administrator managed CFSDB > select name from v$controlfile; NAME ---------------------------------------- <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/control01.ctl <strong><span style="color: red;">/mnt/acfs</span></strong>/cfsdb/control02.ctl CFSDB > select member from v$logfile; MEMBER ---------------------------------------- <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/redo03.log <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/redo02.log <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/redo01.log CFSDB > select name from v$archived_log; NAME ----------------------------------------------------------------- <strong><span style="color: red;">/mnt/acfs</span></strong>/CFSDB/archivelog/2015_07_07/o1_mf_1_7_bsqsor96_.arc CFSDB > select name from v$datafile; NAME ----------------------------------------------------------------- <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/system01.dbf <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/sysaux01.dbf <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/undotbs01.dbf <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/example01.dbf <strong><span style="color: red;">/mnt/acfs</span></strong>/oradata/cfsdb/users01.dbf CFSDB>sho parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string <strong><span style="color: red;"> /mnt/acfs</span></strong> db_recovery_file_dest_size big integer 3000M |
Let’s confirm that the database cfsdb is in archivelog mode
|
1 2 3 4 5 6 7 8 |
CFSDB > archive log list; Database log mode <strong> <span style="color: red;">Archive Mode</span></strong> Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 |
Create a test table HR.EMP with 107 rows in EXAMPLE tablespace:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> create table hr.emp tablespace example as select * from hr.employees; SQL> select owner, table_name, tablespace_name from dba_tables where owner = 'HR' and table_name = 'EMP'; OWNER TABLE_NAME TABLESPACE_NAME ---------- --------------- ------------------------------ HR EMP <strong><span style="color: red;">EXAMPLE</span></strong> SQL> select count(*) from hr.emp; COUNT(*) ---------- <strong> 107</strong> |
Verify that currently there are no snapshots of the cloud file system hosting the database files:
|
1 2 3 4 |
[root@host01 oracle]# acfsutil snap info /mnt/acfs number of snapshots: <strong>0</strong> snapshot space usage: 0 ( 0.00 ) |
Take snapshot (example_dbsnap) of the cloud file system while the database is in backup mode:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> alter database begin backup; Database altered. [root@host01 acfs]# acfsutil snap create example_dbsnap /mnt/acfs acfsutil snap create: Snapshot operation is complete. [root@host01 acfs]# acfsutil snap info /mnt/acfs snapshot name: <strong><span style="color: red;">example_dbsnap</span></strong> snapshot location: <strong><span style="color: red;">/mnt/acfs/.ACFS/snaps/example_dbsnap</span></strong> RO snapshot or RW snapshot: RO parent name: /mnt/acfs snapshot creation time: Tue Jul 7 20:34:18 2015 number of snapshots: 1 snapshot space usage: 9486336 ( 9.04 MB ) SQL> alter database end backup; Database altered. |
Check that all the datafiles, control file and spfile are available in snapshot:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@host01 acfs]# ls -l /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb total 3038112 -rw-r----- 1 oracle oinstall 18956288 Jul 7 20:25 control01.ctl -rw-r----- 1 oracle oinstall 1304174592 Jul 7 20:22 example01.dbf -rw-r----- 1 oracle oinstall 7680 Jul 7 19:38 orapwcfsdb -rw-r----- 1 oracle oinstall 52429312 Jul 7 20:03 redo01.log -rw-r----- 1 oracle oinstall 52429312 Jul 7 20:30 redo02.log -rw-r----- 1 oracle oinstall 52429312 Jul 7 19:44 redo03.log -rw-r----- 1 oracle oinstall 3584 Jul 7 20:11 spfilecfsdb.ora -rw-r----- 1 oracle oinstall 629153792 Jul 7 20:30 sysaux01.dbf -rw-r----- 1 oracle oinstall 828383232 Jul 7 20:30 system01.dbf -rw-r----- 1 oracle oinstall 62922752 Jul 7 20:25 temp01.dbf -rw-r----- 1 oracle oinstall 104865792 Jul 7 20:30 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 7 20:22 users01.dbf |
Insert records in hr.emp so that it has 214 rows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> insert into hr.emp select * from hr.emp; 107 rows created. SQL> commit; Commit complete SQL> select count(*) from hr.emp; COUNT(*) ---------- <strong><span style="color: red;">214</span></strong> |
Simulate loss of datafile for EXAMPLE tablespace:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> ho mv /mnt/acfs/oradata/cfsdb/example01.dbf /mnt/acfs/oradata/cfsdb/example01.bak SQL> alter tablespace example offline; Tablespace altered. SQL> alter tablespace example online; alter tablespace example online * <strong><span style="color: red;">ERROR at line 1:</span></strong> <strong><span style="color: red;">ORA-01157: cannot identify/lock data file 5 - see DBWR trace file</span></strong> <strong><span style="color: red;">ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'.</span></strong> |
Locate the datafile for EXAMPLE tablespace in snapshot example_dbsnap:
|
1 2 3 |
[root@host01 cfsdb]# ls -l /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf -rw-r----- 1 oracle oinstall 1304174592 Jul 7 20:22 <strong><span style="color: red;">/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf</span></strong> |
Copy datafile for EXAMPLE tablespace from snapshot and attempt to bring EXAMPLE tablespace online:
|
1 2 3 4 5 6 7 8 9 10 11 |
[oracle@host01 ~]$ cp /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf /mnt/acfs/oradata/cfsdb/example01.dbf [oracle@host01 ~]$ ls -l /mnt/acfs/oradata/cfsdb/example01.dbf -rw-r----- 1 oracle oinstall 1304174592 Jul 7 20:44 /mnt/acfs/oradata/cfsdb/example01.dbf SQL> alter tablespace example online; alter tablespace example online * <strong><span style="color: red;">ERROR at line 1:</span></strong> <strong><span style="color: red;">ORA-01113: file 5 needs media recovery</span></strong> <strong><span style="color: red;">ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'</span></strong> |
Employ RMAN to perform complete recovery using archived logs in FRA:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
RMAN> recover tablespace example; Starting recover at 07-JUL-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=77 instance=cfsdb1 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:06 Finished recover at 07-JUL-15 SQL> alter tablespace example online; Tablespace altered. SQL> select count(*) from hr.emp; COUNT(*) ---------- <strong><span style="color: red;">214</span></strong> |
Thus, we have been able to perform complete recovery by employing:
- OS commands to copy the datafile from the snapshot example_dbsnap taken while the database was in backup mode.
- RMAN RECOVER command to perform complete recovery using Archived logs stored in FRA (/mnt/acfs).
Now, let us explore if we can catalog the datafile copies stored in the snapshot with RMAN and hence employ RMAN for RESTORE as well as RECOVER.
Catalog all the files in the snapshot example_dbsnap in directory “/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
RMAN> catalog start with '/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/'; searching for all files that match the pattern/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/ List of Files Unknown to the Database ===================================== File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/orapwcfsdb File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/sysaux01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/system01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/users01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/undotbs01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/control01.ctl File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo01.log File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo02.log File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo03.log File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/temp01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/spfilecfsdb.ora Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/sysaux01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/system01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/users01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/undotbs01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/temp01.dbf File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf List of Files Which Were Not Cataloged ======================================= File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/orapwcfsdb RMAN-07518: Reason: Foreign database file DBID: 0 Database Name: File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/control01.ctl RMAN-07519: Reason: Error while cataloging. See alert.log. File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo01.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo02.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo03.log RMAN-07529: Reason: catalog is not supported for this file type File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/spfilecfsdb.ora RMAN-07518: Reason: Foreign database file DBID: 0 Database Name: RMAN> list copy of database; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- --------------- 2 1 A 08-JUL-15 1639217 07-JUL-15 Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/system01.dbf 1 3 A 08-JUL-15 1639217 07-JUL-15 Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/sysaux01.dbf 4 4 A 08-JUL-15 1639217 07-JUL-15 Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/undotbs01.dbf 5 5 A 08-JUL-15 1639217 07-JUL-15 Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf 3 6 A 08-JUL-15 1639217 07-JUL-15 Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/users01.dbf |
It can be seen that all the datafile copies have been catalogued whereas SPfile and controlfile have not been registered with RMAN.
Now we will again simulate the loss of datafile for the EXAMPLE tablespace, and then we’ll attempt to employ RMAN for RESTORE as well RECOVER.
Simulate loss of datafile for EXAMPLE tablespace:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> ho rm /mnt/acfs/oradata/cfsdb/example01.dbf alter tablespace example offline; Tablespace altered. SQL> alter tablespace example online; alter tablespace example online * <strong><span style="color: red;">ERROR at line 1:</span></strong> <strong><span style="color: red;">ORA-01157: cannot identify/lock data file 5 - see DBWR trace file</span></strong> <strong><span style="color: red;">ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'</span></strong> |
Restore EXAMPLE tablespace from the catalogued datafile copy in the snapshot using RMAN and attempt to bring EXAMPLE tablespace online:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
RMAN> restore tablespace example; Starting restore at 08-JUL-15 using channel ORA_DISK_1 channel ORA_DISK_1: restoring datafile 00005 <strong><span style="color: red;">input datafile copy RECID=5 STAMP=884515151 file name=/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf</span></strong> destination for restore of datafile 00005: /mnt/acfs/oradata/cfsdb/example01.dbf channel ORA_DISK_1: copied datafile copy of datafile 00005 output file name=/mnt/acfs/oradata/cfsdb/example01.dbf RECID=0 STAMP=0 Finished restore at 08-JUL-15 SQL> alter tablespace example online; alter tablespace example online * <strong><span style="color: red;">ERROR at line 1:</span></strong> <strong><span style="color: red;">ORA-01113: file 5 needs media recovery</span></strong> <strong><span style="color: red;">ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'</span></strong> |
Employ RMAN to perform complete recovery using archived logs in FRA:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
RMAN> recover tablespace example; Starting recover at 08-JUL-15 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:09 Finished recover at 08-JUL-15 SQL> alter tablespace example online; Tablespace altered. SQL> select count(*) from hr.emp; COUNT(*) ---------- 214 |
Thus, we have been able to perform complete recovery by employing:
- RMAN RESTORE command to RESTORE the datafile from the snapshot example_dbsnap taken while the database was in backup mode.
- RMAN RECOVER command to perform complete recovery using Archived logs stored in FRA (/mnt/acfs).
Conclusion:
- Starting with Oracle Grid Infrastructure 12c (12.1), ACFS supports database files in cluster environment so that entire Oracle databases can be stored inside a cloud file system.
- ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform complete recovery using RMAN RESTORE / RECOVER commands.
Load comments